﻿------Domain
CREATE PROCEDURE usp_GetSingle_Domain
(
	@domain varchar(50),
	@password varchar(32)
)
AS
BEGIN
	
	SELECT A.[id], domain, A.[name], [address], email, A.[createDate], expiredDate, A.[status], themeId, B.name AS ThemeName, B.link, B.[status] AS ThemeStatus
	FROM dbo.Domain A LEFT JOIN dbo.Theme B ON A.themeId = B.id
	WHERE domain = @domain AND [password] = @password
END

GO

CREATE alter PROCEDURE usp_GetAll_Domain
(
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN

	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	SELECT id, domain, name, [address], email, createDate, expiredDate, [status], themeId
	INTO #TEMP
	FROM (
			SELECT id, domain, name, [address], email, createDate, expiredDate, [status], themeId
			FROM dbo.Domain 
		                              
	) AS A ORDER BY createDate DESC;

	 WITH DomainData AS 
	( 
			SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, domain, name, [address], email, createDate, expiredDate, [status], themeId
			FROM #TEMP
			GROUP BY id, domain, name, [address], email, createDate, expiredDate, [status], themeId
	)   
	
	SELECT * 
	FROM DomainData 
	WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
	ORDER BY createDate DESC

	SELECT COUNT(*)
	FROM #TEMP
	
	
END

GO

CREATE PROCEDURE usp_ResetPass_Domain
(
	@id bigint
)
AS
BEGIN
	DECLARE @Err int, @NewPass varchar(8),@PassWord varchar(32)
	SET @Err = 0
	
	SET @NewPass = LEFT(NEWID(), 8)
	
	IF NOT EXISTS(SELECT id FROM Domain WHERE id = @id) BEGIN
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	
	UPDATE Domain
	SET [password] = LOWER(CONVERT(VARCHAR(32),HASHBYTES('MD5',@NewPass),2))
	WHERE id = @id
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Domain Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description, @NewPass AS NewPass
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Update_Domain
(
	@domainID bigint,
	@domainName nvarchar(250),
	@domainAddress nvarchar(250),
	@domainEmail nvarchar(100),
	@status tinyint
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS(SELECT id FROM dbo.Domain WHERE id = @domainID) BEGIN
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description
	END
	
	BEGIN TRANSACTION
	UPDATE dbo.Domain
	SET name = @domainName,
		address = @domainAddress,
		email = @domainEmail,
		[status] = @status
	WHERE id = @domainID
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE dbo.Domain Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Add_Domain
(
	@id bigint,
	@domain varchar(50),
	@password varchar(32),
	@name nvarchar(200) = NULL,
	@address nvarchar(250) = NULL,
	@email nvarchar(100)
)
AS
BEGIN
	
	DECLARE @Err int, @t_id bigint , @t_domain varchar(50)
	DECLARE @t_Theme_Default bigint
	SET @t_Theme_Default = 100000000
	SET @Err = 0
	
	IF EXISTS(SELECT id FROM Domain WHERE id = @id) BEGIN
		SELECT 1 AS Flag,N'Mã Domain đã tồn tại' AS Description
		RETURN
	END
	
	IF EXISTS(SELECT id FROM Domain WHERE domain = @domain) BEGIN
		SELECT 2 AS Flag,N'Domain đã tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	
	INSERT INTO dbo.Domain(id, domain, [password], name, [address], email, createDate, expiredDate, [status], themeId)
				VALUES(@id, @domain, @password, @name, @address, @email, GETDATE(), DATEADD(YEAR, 1, GETDATE()), 1, @t_Theme_Default)
	IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.Domain Error', 16, 1)
		SET @Err = @Err + @@ERROR
		
	
		
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END
GO

CREATE PROCEDURE usp_Edit_Domain
(
	@id bigint,
	@name nvarchar(200) = NULL,
	@address nvarchar(250) = NULL,
	@email nvarchar(100)
)
AS
BEGIN
	DECLARE @Err int, @t_id bigint , @t_domain varchar(50)
	DECLARE @t_Theme_Default bigint
	SET @t_Theme_Default = 100000000
	SET @Err = 0
	
	IF NOT EXISTS(SELECT id FROM Domain WHERE id = @id) BEGIN
		SELECT 1 AS Flag,N'Mã Domain đã tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	
	UPDATE dbo.Domain
	SET name = @name,
		[address] = @address,
		email = @email
	WHERE id = @id
	IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.Domain Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_UpdateStatus_Domain
(
	@id bigint,
	@status tinyint
)
AS
BEGIN
	DECLARE @Err int, @t_id bigint , @t_domain varchar(50)
	DECLARE @t_Theme_Default bigint
	SET @t_Theme_Default = 100000000
	SET @Err = 0
	
	IF NOT EXISTS(SELECT id FROM Domain WHERE id = @id) BEGIN
		SELECT 1 AS Flag,N'Mã Domain đã tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	
	UPDATE dbo.Domain
	SET [status] = @status 
	WHERE id = @id
	IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.Domain Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO
-----

CREATE PROCEDURE usp_Add_About
(
	@AboutId bigint,
	@Type tinyint,
	@DomainID bigint,
	@Content ntext
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Domain WHERE id = @DomainID AND [status] = 1) BEGIN
	
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description 
		RETURN
	END
	
	BEGIN TRANSACTION
	
	INSERT INTO About(id, type, domainId, content)
				VALUES(@AboutId, @Type, @DomainID, @Content)
	IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO About Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

-----About
CREATE PROCEDURE usp_GetSingleByType_About
(
	@domainID bigint,
	@type tinyint
)
AS
BEGIN
	
	SELECT id, type, domainId, content
	FROM About
	WHERE domainId = @domainID AND [type] = @type
	
END

GO



CREATE PROCEDURE usp_Update_About
(
	@AboutId bigint,
	@Type tinyint,
	@DomainID bigint,
	@Content ntext
)
AS
BEGIN
	
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Domain WHERE id = @DomainID AND [status] = 1) BEGIN
	
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description 
		RETURN
	END
	
	IF NOT EXISTS( SELECT id FROM About WHERE id = @AboutId AND domainId = @DomainID AND [type] = @Type) BEGIN
	
		SELECT 2 AS Flag,N'About không tồn tại' AS Description 
		RETURN
	END
	
	BEGIN TRANSACTION
	
	UPDATE About
	SET   Content = @Content
	WHERE id = @AboutId AND domainId = @DomainID AND [type] = @Type
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE About Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Delete_About
(
	@AboutId bigint,
	@Type tinyint,
	@DomainID bigint
)
AS
BEGIN
	
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Domain WHERE id = @DomainID AND [status] = 1) BEGIN
	
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description 
		RETURN
	END
	
	IF NOT EXISTS( SELECT id FROM About WHERE id = @AboutId AND domainId = @DomainID AND [type] = @Type) BEGIN
	
		SELECT 2 AS Flag,N'About không tồn tại' AS Description 
		RETURN
	END
	
	BEGIN TRANSACTION
	
	DELETE FROM About
	WHERE id = @AboutId AND domainId = @DomainID AND [type] = @Type 
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE About Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END


GO

---Control

CREATE PROCEDURE usp_GetByDomainID_Control
(
	@DomainID bigint
)
AS
BEGIN
	SELECT A.id, domainId, controlTypeId, [index], [status]
	FROM [Control] A LEFT JOIN ControlType B ON A.controlTypeId = B.id
	WHERE domainId = @DomainID AND [status] = 1
	ORDER BY [index] ASC
END

GO

CREATE PROCEDURE usp_GetAll_ControlType
AS
BEGIN

	SELECT id, name, [description]
	FROM [Control]
END

GO

CREATE PROCEDURE usp_SetByDomainID_Control
(
	@DomainID bigint,
	@NuControl int,
	@XmlData xml
)
AS
BEGIN
	DECLARE @Err int, @k int ,@id varchar(10), @controlTypeId varchar(10), @index varchar(10)
	SET @Err = 0
	SET @k = 1
	
	
	
	BEGIN TRANSACTION
	
	UPDATE [Control]
	SET	   [status] = 2
	WHERE  domainId = @DomainID AND [status] = 1
	
	WHILE @k <= @NuControl
	BEGIN
		SELECT 
			@id  = R.i.value('@id','varchar(20)'),
			@controlTypeId  = R.i.value('@controltypeid','varchar(20)'),
			@index  = R.i.value('@index','varchar(10)')
		FROM 
			@XmlData.nodes('/root/item')R(i) 
		WHERE 
			R.i.value('@rank','varchar(10)') = @k 
		
		
		INSERT INTO [Control](id, domainId, controlTypeId, [index], [status])
			VALUES		    (@id, @DomainID,@controlTypeId, @index, 1)
		IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO [Control]( Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		SET @k=@k+1
	END
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO



-- Menu 
CREATE PROCEDURE usp_GetList_Menu
(
	@DomainID bigint
)
AS
BEGIN
	SELECT A.id, pageId, B.page AS pagename, [sid], domainId, name, A.[description], [index], A.[status]
	FROM Menu A LEFT JOIN Page B ON A.pageId = B.id
	WHERE domainId = @DomainID AND A.[status] = 1
	ORDER BY [index] DESC
	
END

GO



CREATE PROCEDURE usp_GetList_Page
AS
BEGIN
	SELECT id, page, description, status
	FROM dbo.Page
	WHERE [status] = 1
END

GO

CREATE PROCEDURE usp_Update_Menu
(
	@DomainID bigint,
	@MenuID bigint,
	@MenuSID varchar(50),
	@MenuName nvarchar(50),
	@MenuDes nvarchar(250),
	@MenuStatus tinyint
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Menu WHERE id = @MenuID AND domainId = @DomainID) BEGIN
		SELECT 1 AS Flag,N'MenuID không tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	UPDATE dbo.Menu
	SET [sid] = @MenuSID,
		[name] = @MenuName,
		[description] = @MenuDes,
		[status] = @MenuStatus
	WHERE id = @MenuID AND domainId = @DomainID
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE dbo.Menu Error', 16, 1)
				SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE alter PROCEDURE usp_Add_Menu
(
	@DomainID bigint,
	@MenuID bigint,
	@MenupageId bigint,
	@MenuSID varchar(50),
	@MenuName nvarchar(50),
	@MenuDes nvarchar(250)
)
AS
BEGIN
	DECLARE @Err int, @index int
	SET @Err = 0
	
	IF EXISTS( SELECT id FROM dbo.Menu WHERE pageId = @MenupageId AND domainId = @DomainID) BEGIN
		SELECT 1 AS Flag,N'Page đã tồn tại tồn tại' AS Description
		RETURN
	END
	
	IF EXISTS( SELECT id FROM dbo.Menu WHERE id = @MenuID) BEGIN
		SELECT 2 AS Flag,N'Menu không tồn tại' AS Description
		RETURN
	END
	
	
	SELECT @index = ISNULL(MAX([index]),0)
	FROM dbo.Menu
	WHERE domainId = @DomainID
	
	SET @index = @index + 1
	
	BEGIN TRANSACTION
	INSERT INTO dbo.Menu(id, pageId, [sid], domainId, name, [description], [index], [status])
		VALUES		    (@MenuID, @MenupageId, @MenuSID, @DomainID, @MenuName, @MenuDes, @index, 1)
	IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.Menu Error', 16, 1)
				SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_GetSingle_Menu
(
	@DomainID bigint,
	@MenuID bigint
)
AS
BEGIN
	SELECT id, pageId, [sid], domainId, name, [description], [index], [status]
	FROM dbo.Menu WHERE id = @MenuID AND domainId = @DomainID
END

GO

CREATE PROCEDURE usp_Delete_Menu
(
	@DomainID bigint,
	@MenuID bigint
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Menu WHERE id = @MenuID AND domainId = @DomainID) BEGIN
		SELECT 1 AS Flag,N'Menu không tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	DELETE FROM dbo.Menu
	WHERE id = @MenuID AND domainId = @DomainID
	IF @@ROWCOUNT = 0 RAISERROR ('DELETE FROM dbo.Menu Error', 16, 1)
				SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_SetIndexByDomainID_Menu
(
	@DomainID bigint,
	@NuControl int,
	@XmlData xml
)
AS
BEGIN
	DECLARE @Err int, @k int ,@id varchar(20), @pageId varchar(10),@sid varchar(50), @index varchar(10), @name nvarchar(50), @description nvarchar(250)
	SET @Err = 0
	SET @k = 1
	
	DECLARE @t_id bigint, @t_pageid bigint, @t_sid varchar(50), @t_name nvarchar(50), @t_description nvarchar(250)
	
	BEGIN TRANSACTION
	
	WHILE @k <= @NuControl
	BEGIN
		SELECT 
			@id  = R.i.value('@id','varchar(20)'),
			@index  = R.i.value('@index','varchar(10)')
		FROM 
			@XmlData.nodes('/root/item')R(i) 
		WHERE 
			R.i.value('@rank','varchar(10)') = @k 
		
		SET @t_id = CONVERT(bigint,@id)
		
		SELECT @t_pageid = pageId,
				@t_sid =sid,
				@t_name = name,
				@t_description =description
		FROM Menu
		WHERE domainId = @DomainID AND id = @t_id
		
		IF @@ROWCOUNT = 0 BEGIN
			SET @Err = @Err + 1
		END
		
		IF @Err <> 0 BEGIN
		
			DELETE FROM Menu
			WHERE domainId = @DomainID AND id = @id
			
			INSERT INTO dbo.Menu(id, pageId, [sid], domainId, name, [description], [index], [status])
				VALUES		    (@t_id, @t_pageid, @t_sid, @DomainID, @t_name, @t_description, @index, 1)
			IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.Menu Error', 16, 1)
				SET @Err = @Err + @@ERROR
		END
	
		SET @k=@k+1
	END
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO



--Category
CREATE PROCEDURE usp_GetAllList_Category
(
	@DomainID bigint
)
AS
BEGIN
	
	SELECT id, domainId, [sid], name, parentId, [index], [status]
	FROM Category
	WHERE domainId = @DomainID AND [status] = 1
	
END

GO

CREATE PROCEDURE usp_GetListByParentID_Category
(
	@DomainID bigint,
	@ParentID bigint
)
AS
BEGIN

	SELECT id, domainId, [sid], name, parentId, [index], [status]
	FROM Category
	WHERE domainId = @DomainID AND parentId = @ParentID AND [status] = 1
	ORDER BY [index] ASC
END

GO

--CSS
CREATE PROCEDURE usp_Get_CSS
(
	@DomainID bigint
)
AS
BEGIN
	
	SELECT A.id, cssClassId, B.name AS CssClass, domainId, value
	FROM dbo.Css A LEFT JOIN CssClass B ON A.cssClassId = B.id
	WHERE domainId = @DomainID AND B.[status] = 1
END

GO

--PROduct
CREATE PROCEDURE usp_GetDetail_Product
(
	@id bigint
	
)
AS
BEGIN
	
	SELECT id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
	FROM dbo.Product
	WHERE id = @id AND [status] = 1
END

GO

CREATE alter PROCEDURE usp_GetListByCategory_Product
(
	@DomainID bigint,
	@CategoryId bigint,
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN
	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	SELECT id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
	INTO #TEMP
	FROM (
			SELECT A.id, categoryId, A.name, A.[sid], price, imgUrl, [description], content, createDate, A.[status]
			FROM dbo.Product A LEFT JOIN Category B ON A.categoryId = B.id
			WHERE categoryId = @CategoryId AND domainId = @DomainID  AND A.[status] = 1
		                              
	) AS A ORDER BY createDate DESC;

	 WITH ProductData AS 
	( 
			SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
			FROM #TEMP
			GROUP BY id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
	)   
	
	SELECT * 
	FROM ProductData 
	WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
	ORDER BY createDate DESC

	SELECT COUNT(*)
	FROM #TEMP
END

GO

CREATE PROCEDURE usp_GetListByDomain_Product
(
	@DomainId bigint,
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN
	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	SELECT id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
	INTO #TEMP
	FROM (
			SELECT B.id, categoryId, B.name, B.[sid], price, imgUrl, [description], content, createDate, B.[status]
			FROM dbo.Category A LEFT JOIN dbo.Product B ON A.id = B.categoryId
			WHERE A.domainId = @DomainId AND A.[status] = 1 AND B.[status] = 1
		                              
	) AS A ORDER BY createDate DESC;

	 WITH ProductData AS 
	( 
			SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
			FROM #TEMP
			GROUP BY id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
	)   
	
	SELECT * 
	FROM ProductData 
	WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
	ORDER BY createDate DESC

	SELECT COUNT(*)
	FROM #TEMP
END

GO

CREATE PROCEDURE usp_SearchPriceByDomain_Product
(
	@DomainId bigint,
	@KeySearch nvarchar(250) = NULL,
	@MinPrice money = NULL,
	@MaxPrice money = NULL,
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN
	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	DECLARE @t_KeySearch nvarchar(250)
	DECLARE @t_MinPrice money, @t_MaxPrice money
	
	SET @t_KeySearch = ISNULL(@KeySearch,'')
	SET @t_MinPrice  = ISNULL(@MinPrice, 0)
	SET @t_MaxPrice = ISNULL(@MaxPrice, 0)
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	IF @t_KeySearch <> '' AND @t_MaxPrice <> 0 AND @t_MinPrice <> 0 BEGIN
		
		SELECT id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
		INTO #TEMP
		FROM (
				SELECT B.id, categoryId, B.name, B.[sid], price, imgUrl, [description], content, createDate, B.[status]
				FROM dbo.Category A LEFT JOIN dbo.Product B ON A.id = B.categoryId
				WHERE A.domainId = @DomainId AND A.[status] = 1 AND B.[status] = 1 AND B.name LIKE N'%' +  @t_KeySearch + '%' AND B.price BETWEEN @t_MinPrice AND @t_MaxPrice
			                              
		) AS A ORDER BY createDate DESC;

		 WITH ProductData AS 
		( 
				SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
				FROM #TEMP
				GROUP BY id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
		)   
		
		SELECT * 
		FROM ProductData 
		WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
		ORDER BY createDate DESC

		SELECT COUNT(*)
		FROM #TEMP
	END
	ELSE IF @t_KeySearch <> '' BEGIN
	
		SELECT id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
		INTO #TEMP1
		FROM (
				SELECT B.id, categoryId, B.name, B.[sid], price, imgUrl, [description], content, createDate, B.[status]
				FROM dbo.Category A LEFT JOIN dbo.Product B ON A.id = B.categoryId
				WHERE A.domainId = @DomainId AND A.[status] = 1 AND B.[status] = 1 AND B.name LIKE N'%' +  @t_KeySearch + '%'
			                              
		) AS A ORDER BY createDate DESC;

		 WITH ProductData AS 
		( 
				SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
				FROM #TEMP1
				GROUP BY id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
		)   
		
		SELECT * 
		FROM ProductData 
		WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
		ORDER BY createDate DESC

		SELECT COUNT(*)
		FROM #TEMP1
	END
	ELSE BEGIN
		
		SELECT id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
		INTO #TEMP2
		FROM (
				SELECT B.id, categoryId, B.name, B.[sid], price, imgUrl, [description], content, createDate, B.[status]
				FROM dbo.Category A LEFT JOIN dbo.Product B ON A.id = B.categoryId
				WHERE A.domainId = @DomainId AND A.[status] = 1 AND B.[status] = 1
			                              
		) AS A ORDER BY createDate DESC;

		 WITH ProductData AS 
		( 
				SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
				FROM #TEMP2
				GROUP BY id, categoryId, name, [sid], price, imgUrl, [description], content, createDate, [status]
		)   
		
		SELECT * 
		FROM ProductData 
		WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
		ORDER BY createDate DESC

		SELECT COUNT(*)
		FROM #TEMP2
	END
END

GO

CREATE PROCEDURE usp_GetList_ProductImg
(
	@productId bigint
)
AS
BEGIN
	
	SELECT id, productId, imgUrl, createDate, status
	FROM dbo.ProductImg
	WHERE productId = @productId AND [status] = 1
	
END

GO

CREATE PROCEDURE usp_SetList_ProductImg
(
	@ProductID bigint,
	@NuControl int,
	@XmlData xml
)
AS
BEGIN
	DECLARE @Error int, @k int ,@id varchar(20), @imgUrl varchar(250), @index varchar(10)
	SET @Error = 0
	SET @k = 1
	
	BEGIN TRANSACTION
	
	DELETE FROM dbo.ProductImg
	WHERE id = @ProductID
	
	WHILE @k <= @NuControl
	BEGIN
		SELECT 
			@id  = R.i.value('@id','varchar(20)'),
			@imgUrl = R.i.value('@imgUrl','varchar(50)')
		FROM 
			@XmlData.nodes('/root/item')R(i) 
		WHERE 
			R.i.value('@rank','varchar(10)') = @k 
		
		
		INSERT INTO dbo.ProductImg(id, productId, imgUrl, createDate, status)
			VALUES		    (@id, @ProductID, @imgUrl, GETDATE(), 1)
		IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.ProductImg Error', 16, 1) 
		SET @Error = @Error + @@ERROR
	
		SET @k=@k+1
	END
	
	IF @Error = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag, N'Insert data thành công' as Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhập dữ liệu' as Description
	END
END


CREATE PROCEDURE usp_Add_Product
(
	@Domainid bigint,
	@id bigint,
	@categoryId bigint, 
	@name nvarchar(250), 
	@sid varchar(250), 
	@price money, 
	@imgUrl varchar(250), 
	@description nvarchar(500), 
	@content ntext
)
AS
BEGIN
	
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Category WHERE domainId = @Domainid AND id = @categoryId AND [status] = 1) BEGIN
		SELECT 1 AS Flag,N'Domain không hợp lệ' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	INSERT INTO dbo.Product(id, categoryId, name, sid, price, imgUrl, description, content, createDate, status)
				VALUES(@id, @categoryId, @name, @sid, @price, @imgUrl, @description, @content, GETDATE(), 1)
	IF @@ROWCOUNT = 0 RAISERROR ('INSERT INTO dbo.Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Update_Product
(
	@Domainid bigint,
	@id bigint,
	@name nvarchar(250), 
	@sid varchar(250), 
	@price money, 
	@imgUrl varchar(250), 
	@description nvarchar(500), 
	@content ntext
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS (SELECT A.id FROM dbo.Product A LEFT JOIN dbo.Category B ON A.categoryId = B.id
					WHERE A.id = @id AND B.domainId = @Domainid) BEGIN
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description
		RETURN		
	END
	
	BEGIN TRANSACTION
	
	UPDATE Product
	SET name = @name,
		[sid]  = @sid,
		price  = @price,
		imgUrl =@imgUrl,
		description =@description,
		content = @content
	WHERE id= @id
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Delete_Product
(
	@Domainid bigint,
	@productID bigint
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	IF NOT EXISTS (SELECT A.id FROM dbo.Product A LEFT JOIN dbo.Category B ON A.categoryId = B.id
					WHERE A.id = @productID AND B.domainId = @Domainid) BEGIN
		SELECT 1 AS Flag,N'Domain không tồn tại' AS Description
		RETURN		
	END
	
	BEGIN TRANSACTION
	
	DELETE FROM Product
	WHERE id = @productID
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

--Theme
CREATE PROCEDURE usp_GetList_Theme
(
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN
	
	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	SELECT id, name, price, [description], createDate, link, [status]
	INTO #TEMP
	FROM (
			SELECT id, name, price, [description], createDate, link, [status]
			FROM dbo.Theme
			WHERE [status] = 1
		                              
	) AS A ORDER BY createDate DESC;

	 WITH ThemeData AS 
	( 
			SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, name, price, [description], createDate, link, [status]
			FROM #TEMP
			GROUP BY id, name, price, [description], createDate, link, [status]
	)   
	
	SELECT * 
	FROM ThemeData 
	WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
	ORDER BY createDate DESC

	SELECT COUNT(*)
	FROM #TEMP
	
	
END

GO 


CREATE PROCEDURE usp_GetSingle_Theme
(
	@ThemeID bigint
)
AS
BEGIN
	SELECT id, name, price, description, createDate, link, status
	FROM dbo.Theme
	WHERE id = @ThemeID
END

GO

CREATE PROCEDURE usp_Update_Theme
(
	@ThemeID bigint,
	@ThemeName nvarchar(250),
	@ThemePrice money,
	@ThemeDescription nvarchar(250),
	@ThemeLink varchar(250),
	@ThemeStatus tinyint
	
)
AS
BEGIN
	DECLARE @Error int
	SET @Error = 0
	
	IF NOT EXISTS( SELECT id FROM dbo.Theme WHERE id = @ThemeID) BEGIN
		SELECT 1 AS Flag,N'Theme không tồn tại' AS Description
		RETURN
	END
	
	BEGIN TRANSACTION
	UPDATE dbo.Theme
	SET name = @ThemeName,
		price = @ThemePrice,
		description = @ThemeDescription,
		link = @ThemeLink,
		status = @ThemeStatus
	WHERE id = @ThemeID
	IF @@ROWCOUNT = 0 RAISERROR('UPDATE dbo.Theme', 16, 1)
		SET @Error = @Error + @@ERROR
	
	IF @Error = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Add_Theme
(
	@ThemeID bigint,
	@ThemeName nvarchar(250),
	@ThemePrice money,
	@ThemeDescription nvarchar(250),
	@ThemeLink varchar(250)
)
AS
BEGIN
	DECLARE @Error int
	SET @Error = 0
	
	IF EXISTS( SELECT id FROM dbo.Theme WHERE id = @ThemeID)BEGIN
		SELECT 1 as Flag, N'ThemeID đã tồn tại' as Description
		RETURN
	END
	
	BEGIN TRANSACTION
	INSERT INTO dbo.Theme(id, name, price, description, createDate, link, status)
				VALUES(@ThemeID, @ThemeName, @ThemePrice, @ThemeDescription, GETDATE(), @ThemeLink, 1)
				
	IF @@ROWCOUNT = 0 RAISERROR('INSERT INTO dbo.Theme',16,1)
		SET @Error = @Error + @@ERROR
	
	--COMMIT
	IF @Error = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 as Flag, N'Insert Data thành công' as Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 as Flag, N'Lỗi cập nhập dữ liệu' as Description
	END
END

GO
-- News
CREATE PROCEDURE usp_GetList_News
(
	@Domainid bigint,
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN
	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	SELECT id, domainId, name, imgUrl, [description], content, createDate, [status]
	INTO #TEMP
	FROM (
			SELECT id, domainId, name, imgUrl, description, content, createDate, status
			FROM dbo.News
			WHERE domainId = @Domainid AND [status] = 1
		                              
	) AS A ORDER BY createDate DESC;

	 WITH NewsData AS 
	( 
			SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, domainId, name, imgUrl, [description], content, createDate, [status]
			FROM #TEMP
			GROUP BY id, domainId, name, imgUrl, [description], content, createDate, [status]
	)   
	
	SELECT * 
	FROM NewsData 
	WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
	ORDER BY createDate DESC

	SELECT COUNT(*)
	FROM #TEMP
END

GO

CREATE PROCEDURE usp_GetSingle_News
(
	@DomainID bigint,
	@NewsID bigint
)
AS
BEGIN
	SELECT id, domainId, name, imgUrl, description, content, createDate, status
	FROM dbo.News
	WHERE id = @NewsID
END

GO

CREATE PROCEDURE usp_Add_News
(
	@Newsid bigint, 
	@domainId bigint, 
	@name nvarchar(250), 
	@imgUrl varchar(250), 
	@description nvarchar(1000), 
	@content ntext
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	INSERT INTO dbo.News(id, domainId, name, imgUrl, description, content, createDate, status)
			VALUES(@Newsid, @domainId, @name, @imgUrl, @description, @content, GETDATE(), 1)
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Delete_News
(
	@Newsid bigint, 
	@domainId bigint
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	DELETE FROM dbo.News
	WHERE id =@Newsid AND domainId = @domainId
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Update_News
(
	@DomainID bigint,
	@NewsID	bigint,
	@NewsName nvarchar(250),
	@NewsimgUrl varchar(250),
	@NewsDes nvarchar(1000),
	@NewsContent ntext
)
AS
BEGIN
	DECLARE @Error int
	SET @Error =0
	
	IF NOT EXISTS( SELECT id FROM dbo.News WHERE id = @NewsID AND domainId = @DomainID) BEGIN
		SELECT 0 AS Flag , N'Insert Data thành công' as Description
		RETURN
	END
	
	BEGIN TRANSACTION
	
	UPDATE dbo.News
	SET name = @NewsName,
		imgUrl = @NewsimgUrl,
		[description] = @NewsDes,
		content = @NewsContent
	WHERE id = @NewsID AND domainId = @DomainID
	IF @@ROWCOUNT = 0 RAISERROR('UPDATE dbo.News', 16 ,1)
		SET @Error = @Error + @@ERROR
	
	--COMMIT
	IF @Error = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag , N'Insert Data thành công' as Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag , N'Lỗi cập nhập dữ liệu' as Description
	END
END
--
GO

-- Promotion
CREATE PROCEDURE usp_GetList_Promotion
(
	@Domainid bigint,
	@PageSize int, 
    @PageNumber int
)
AS
BEGIN
	DECLARE @RowStart int 
	DECLARE @RowEnd int 
	
	SET @PageNumber = @PageNumber -1 
	SET @RowStart = @PageSize * @PageNumber + 1; 
	SET @RowEnd = @RowStart + @PageSize - 1 ;
	
	SELECT id, domainId, name, imgUrl, [description], content, createDate, [status]
	INTO #TEMP
	FROM (
			SELECT id, domainId, name, imgUrl, description, content, createDate, status
			FROM dbo.Promotion
			WHERE domainId = @Domainid AND [status] = 1
		                              
	) AS A ORDER BY createDate DESC;

	 WITH NewsData AS 
	( 
			SELECT ROW_NUMBER() OVER (order by createDate DESC) as RowNumber, id, domainId, name, imgUrl, [description], content, createDate, [status]
			FROM #TEMP
			GROUP BY id, domainId, name, imgUrl, [description], content, createDate, [status]
	)   
	
	SELECT * 
	FROM NewsData 
	WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd
	ORDER BY createDate DESC

	SELECT COUNT(*)
	FROM #TEMP
END

GO

CREATE PROCEDURE usp_GetSingle_Promotion
(
	@DomainID bigint,
	@PromotionID bigint
)
AS
BEGIN
	SELECT id, domainId, name, imgUrl, description, content, createDate, status
	FROM dbo.Promotion
	WHERE id = @PromotionID
END

GO

CREATE PROCEDURE usp_Add_Promotion
(
	@Promotionid bigint, 
	@domainId bigint, 
	@name nvarchar(250), 
	@imgUrl varchar(250), 
	@description nvarchar(1000), 
	@content ntext
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	INSERT INTO dbo.Promotion(id, domainId, name, imgUrl, description, content, createDate, status)
			VALUES(@Promotionid, @domainId, @name, @imgUrl, @description, @content, GETDATE(), 1)
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Delete_Promotion
(
	@Promotionid bigint, 
	@domainId bigint
)
AS
BEGIN
	DECLARE @Err int
	SET @Err = 0
	
	DELETE FROM dbo.Promotion
	WHERE id =@Promotionid AND domainId = @domainId
	IF @@ROWCOUNT = 0 RAISERROR ('UPDATE Product Error', 16, 1)
		SET @Err = @Err + @@ERROR
	
		-- COMMIT
	IF @Err = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag,N'Insert data thành công' AS Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag, N'Lỗi cập nhật dữ liệu' AS Description		
	END
END

GO

CREATE PROCEDURE usp_Update_Promotion
(
	@DomainID bigint,
	@PromotionID	bigint,
	@PromotionName nvarchar(250),
	@PromotionimgUrl varchar(250),
	@PromotionDes nvarchar(1000),
	@PromotionContent ntext
)
AS
BEGIN
	DECLARE @Error int
	SET @Error =0
	
	IF NOT EXISTS( SELECT id FROM dbo.Promotion WHERE id = @PromotionID AND domainId = @DomainID) BEGIN
		SELECT 0 AS Flag , N'Insert Data thành công' as Description
		RETURN
	END
	
	BEGIN TRANSACTION
	
	UPDATE dbo.Promotion
	SET name = @PromotionName,
		imgUrl = @PromotionimgUrl,
		[description] = @PromotionDes,
		content = @PromotionContent
	WHERE id = @PromotionID AND domainId = @DomainID
	IF @@ROWCOUNT = 0 RAISERROR('UPDATE dbo.News', 16 ,1)
		SET @Error = @Error + @@ERROR
	
	--COMMIT
	IF @Error = 0 BEGIN
		COMMIT TRANSACTION
		SELECT 0 AS Flag , N'Insert Data thành công' as Description
	END
	ELSE BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 AS Flag , N'Lỗi cập nhập dữ liệu' as Description
	END
END
--



